import openpyxl
from collections import Counter

# 打开 Excel 文件
workbook = openpyxl.load_workbook(filename='C:/Users/longxianliang/PycharmProjects/pythonProject1/majun_cve_data.xlsx')
sheet = workbook.active

# 统计每个 affected_software 值出现的次数
counter = Counter()
for row in sheet.iter_rows(min_row=2, values_only=True):  # 从第二行开始迭代
    affected_software = row[2]
    if affected_software:
        software_list = affected_software.split(', ')
        counter.update(software_list)

# 创建一个新的 Excel 工作簿
result_workbook = openpyxl.Workbook()
result_sheet = result_workbook.active
result_sheet.title = "Affected Software Count"

# 添加表头
result_sheet.append(["affected_software", "count"])

# 将数据写入 Excel 表格
for software, count in counter.items():
    result_sheet.append([software, count])

# 保存 Excel 文件
result_workbook.save("affected_software_count.xlsx")
